"""
Case Type   : GSC功能模块
Case Name   : 备机gs_gsc_clean清理共享表的全局系统缓存
Create At   : 2023/05/24
Owner       : opentestcase024
Description :
    1、打开gsc开关;
    2、重启数据库生效;
    3、创建数据库;
    4、创建表并查询;
    5、备机查询所有库并进行连接;
    6、查看syscache占用情况;
    6.1、备机查看gs_gsc_memory_detail占用情况;
    6.2、备机查看gs_gsc_dbstat_info占用情况;
    6.3、备机查看gs_gsc_table_detail占用情况;
    6.4、备机查看gs_gsc_catalog_detail占用情况;
    7、清理共享表的全局系统缓存;
    8、查看syscache占用情况;
    8.1、备机查看gs_gsc_memory_detail占用情况;
    8.2、备机查看gs_gsc_dbstat_info占用情况;
    8.3、备机查看gs_gsc_table_detail占用情况;
    8.4、备机查看gs_gsc_catalog_detail占用情况; 
Expect      :
    1、打开gsc开关; 修改成功
    2、重启数据库生效; 重启成功
    3、创建数据库; 创建成功
    4、创建表并查询; 操作成功
    5、备机查询所有库并进行连接; 连接成功
    6、查看syscache占用情况; 缓存信息正确
    6.1、备机查看gs_gsc_memory_detail占用情况; 缓存库数量为所有库数量
    6.2、备机查看gs_gsc_dbstat_info占用情况; 缓存库数量为所有库数量
    6.3、备机查看gs_gsc_table_detail占用情况; 非0库、0库缓存结果大于0
    6.4、备机查看gs_gsc_catalog_detail占用情况; 非0库、0库缓存结果大于0
    7、清理共享表的全局系统缓存; 清除成功
    8、查看syscache占用情况; 缓存信息正确
    8.1、备机查看gs_gsc_memory_detail占用情况; 缓存库数量为所有库数量
    8.2、备机查看gs_gsc_dbstat_info占用情况; 缓存库数量为所有库数量
    8.3、备机查看gs_gsc_table_detail占用情况; 非0库缓存结果不减少，0库缓存结果减少
    8.4、备机查看gs_gsc_catalog_detail占用情况; 非0库缓存结果不减少，0库缓存结果减少（线程池模式除外）
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
    modified by peilinqian at 2024-6-17: 用例适配资源池化
"""
import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.db_name = 'db_gsc0053'
        self.tb_name = 'tb_gsc0053'
        self.com = Common()
        self.constant = Constant()
        self.sta1_sh = CommonSH('Standby1DbUser')

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = f'----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表并查询; expect:操作成功----'
        self.log.info(step_txt)
        create_tb_sql = f"create table {self.tb_name} " \
            f"(c_integer integer,c_tinyint tinyint," \
            f"c_smallint smallint,c_binary_integer binary_integer," \
            f"c_bigint bigint,c_numeric numeric(10,4)," \
            f"c_number number(10,4),c_smallserial smallserial);" \
            f"select * from {self.tb_name};"
        create_result = pri_sh.execut_db_sql(create_tb_sql,
                                             dbname=f'{self.db_name}')
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)
        self.assertIn('0 rows', create_result, '执行失败:' + step_txt)

        step_txt = '----step5:备机查询所有库并进行连接; expect:连接成功----'
        self.log.info(step_txt)
        select_db_sql = "select datname from pg_database where datname " \
                        "not like('template%');"
        select_db_result = self.sta1_sh.execut_db_sql(select_db_sql)
        self.log.info(select_db_result)
        db_list = select_db_result.splitlines()[2:-1:1]
        for i in db_list:
            con_result = self.sta1_sh.execut_db_sql(
                'select current_database();',
                dbname=f'{i}')
            self.log.info(con_result)
            self.assertIn(i, con_result, '执行失败:' + step_txt)

        self.log.info('缓存所有库数量为：所有库+0库+1库')
        self.total_db_num = len(db_list) + 2

        step_txt = '----step6:查看syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_sql1 = "select * from gs_gsc_memory_detail;"
        check_sql2 = "select * from gs_gsc_dbstat_info();"
        check_sql3 = "select count(*) from gs_gsc_table_detail() " \
                     "where database_oid <>0;"
        check_sql3_1 = "select count(*) from gs_gsc_table_detail(0);"
        check_sql4 = "select count(*) from gs_gsc_catalog_detail() " \
                     "where database_id <>0;"
        check_sql4_1 = "select count(*) from gs_gsc_catalog_detail(0);"

        step_txt = '----step6.1:备机查看gs_gsc_memory_detail占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result6_1 = self.check_select(check_sql1, True)
        self.assertEqual(self.total_db_num, check_result6_1,
                         '执行失败:' + step_txt)
        step_txt = '----step6.2:备机查看gs_gsc_dbstat_info占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result6_2 = self.check_select(check_sql2, True)
        self.assertEqual(self.total_db_num, check_result6_2,
                         '执行失败:' + step_txt)
        step_txt = '----step6.3:备机查看gs_gsc_table_detail占用情况; ' \
                   'expect:非0库、0库缓存结果大于0----'
        self.log.info(step_txt)
        check_result6_3 = self.check_select(check_sql3)
        self.assertGreater(check_result6_3, 0, '执行失败:' + step_txt)
        check_result6_3_1 = self.check_select(check_sql3_1)
        self.assertGreater(check_result6_3_1, 0, '执行失败:' + step_txt)
        step_txt = '----step6.4:备机查看gs_gsc_catalog_detail占用情况; ' \
                   'expect:非0库、0库缓存结果大于0----'
        self.log.info(step_txt)
        check_result6_4 = self.check_select(check_sql4)
        self.assertGreater(check_result6_4, 0, '执行失败:' + step_txt)
        check_result6_4_1 = self.check_select(check_sql4_1)
        self.assertGreater(check_result6_4_1, 0, '执行失败:' + step_txt)

        step_txt = '----step7:清理共享表的全局系统缓存; expect:清除成功----'
        self.log.info(step_txt)
        clean_sql = f"select * from gs_gsc_clean(0);"
        clean_result = self.sta1_sh.execut_db_sql(clean_sql)
        self.log.info(clean_result)
        self.assertEqual(clean_result.splitlines()[-2].strip().lower(), 't',
                         '执行失败:' + step_txt)

        step_txt = '----step8:查看syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        step_txt = '----step8.1:备机查看gs_gsc_memory_detail占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result8_1 = self.check_select(check_sql1, True)
        self.assertEqual(self.total_db_num, check_result8_1,
                         '执行失败:' + step_txt)
        step_txt = '----step8.2:备机查看gs_gsc_dbstat_info占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result8_2 = self.check_select(check_sql2, True)
        self.assertEqual(self.total_db_num, check_result8_2,
                         '执行失败:' + step_txt)
        step_txt = '----step8.3:备机查看gs_gsc_table_detail占用情况; ' \
                   'expect:非0库缓存结果不减少，0库缓存结果减少----'
        self.log.info(step_txt)
        check_result8_3 = self.check_select(check_sql3)
        self.assertGreaterEqual(check_result8_3, check_result6_3,
                                '执行失败:' + step_txt)
        check_result8_3_1 = self.check_select(check_sql3_1)
        self.assertLess(check_result8_3_1, check_result6_3_1,
                        '执行失败:' + step_txt)
        step_txt = '----step8.4:备机查看gs_gsc_catalog_detail占用情况; ' \
                   'expect:非0库缓存结果不减少，0库缓存结果减少（线程池模式除外）----'
        self.log.info(step_txt)
        check_result8_4 = self.check_select(check_sql4)
        self.assertGreaterEqual(check_result8_4, check_result6_4,
                                '执行失败:' + step_txt)
        check_result8_4_1 = self.check_select(check_sql4_1)
        self.assertLessEqual(check_result8_4_1, check_result6_4_1,
                             '执行失败:' + step_txt)

    def check_select(self, sql, is_total_count=False):
        """
        :param: sql: 查询对象sql
        :return: 查询结果count数
        """
        select_result = self.sta1_sh.execut_db_sql(sql)
        self.log.info(select_result)
        if is_total_count:
            return int(select_result.splitlines()[-1].split()[0][1:])
        else:
            return int(select_result.splitlines()[-2].strip())

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc('set',
                                             self.constant.GSGUC_SUCCESS_MSG,
                                             f"enable_global_syscache "
                                             f"={self.init_para1}")

            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            status = pri_sh.exec_cm_ctl(mode='query', param='-Cv')
            self.log.info(status)
            p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
            if 'Primary' in p_status and "cluster_state   : Normal" in status:
                self.log.info('---集群状态正常，无需恢复为初始状态---')
            else:
                recovery = pri_sh.exec_cm_ctl(mode='switchover',
                                                   param='-a')
                self.log.info(recovery)
                time.sleep(10)
            self.status_result = pri_sh.get_db_cluster_status('status')

        self.log.info(f'----{os.path.basename(__file__)}:end----')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)
